"""
Case Type   : Interface
Case Name   : 通过ODBC连接数据库创建包含mediumblob类型表
Create At   : 2022/08/22
Owner       : lvlintao
Description :
    1.安装unixODBC
    2.配置数据库pg_hba.conf文件
    3.创建兼容b库
    4.检查插件
    5.配置数据源
    6.检查依赖库
    7.连接数据库
    8.编译脚本
    9.执行脚本
    10.清理环境
Expect      :
    1.安装成功
    2.配置完成
    3.成功
    4.成功
    5.配置完成
    6.依赖库配置完成
    7.数据库连接成功
    8.成功
    9.成功
    10.成功
History     :
    Modified by lvlintao 2025.8.12 优化用例步骤，centOS环境libstdc版本过低，修改为引用数据库三方库libstdc
"""
import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Security(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.userNode = Node('PrimaryDbUser')
        self.primary_root = Node('PrimaryRoot')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        dir_path = macro.DB_INSTANCE_PATH
        for num in range(macro.DB_INSTANCE_PATH.count('/')):
            dir_path = os.path.dirname(dir_path)
        self.local_path = os.path.join(dir_path, 'usr', 'local', 'odbclib')
        self.targetpath = os.path.join(dir_path, 'etc')
        self.odbc_lib_path = os.path.join(self.local_path, 'odbc', 'lib')
        self.sourcefile = os.path.join(self.local_path, "odbcsrc")
        self.compile = 'ODBCDateType'
        self.db_name = 'db_mediumblob_case46'

    def test_odbc(self):
        text = '------step1:安装unixODBC;expect:成功------'
        self.log.info(text)
        self.common.install_odbc(self.primary_root, self.local_path,
                                 'Complete!')

        text = '-----step2:配置数据库pg_hba.conf文件;expect:成功-----'
        self.log.info(text)
        cp_cmd = f"cp {os.path.join(macro.DB_INSTANCE_PATH, 'pg_hba.conf')} " \
                 f"{os.path.join(macro.DB_INSTANCE_PATH, 'pg_hba_bak.conf')};" \
                 f"ls {macro.DB_INSTANCE_PATH}"
        res = self.userNode.sh(cp_cmd).result()
        self.assertIn('pg_hba_bak.conf', res, 'hba文件备份失败')
        exe_cmd2 = f'source {macro.DB_ENV_PATH};' \
                   f'gs_guc reload -D {macro.DB_INSTANCE_PATH} -h ' \
                   f'"host {self.userNode.db_name} {self.userNode.db_user} ' \
                   f'{self.userNode.db_host}/32 sha256"'
        msg2 = self.userNode.sh(exe_cmd2).result()
        self.assertIn(self.constant.GSGUC_SUCCESS_MSG, msg2, text)

        text = '-----step3:创建兼容b库数据库;expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} " \
                  f"dbcompatibility ='B' encoding ='UTF8';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res,
                      '执行失败' + text)

        text = '-----step4:b库下查询dolphin插件;expect:自动加载成功,无需手动创建-----'
        self.log.info(text)
        sql_cmd = "select extname from pg_extension where extname ='dolphin';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('extname', sql_res, '查询失败')
        self.assertIn('dolphin', sql_res, '执行失败' + text)

        text = '-----step5:配置数据源;expect:成功-----'
        self.log.info(text)

        ini_content = [self.userNode.db_host, self.db_name,
                       self.userNode.db_user, self.userNode.db_password,
                       self.userNode.db_port]
        odbc_ini = self.common.set_odbc_ini(self.primary_root, ini_content,
                                            self.local_path)
        self.assertTrue(odbc_ini, text)
        odbcinst_ini = self.common.set_odbcinst_ini(
            self.primary_root, self.local_path, self.odbc_lib_path)
        self.assertTrue(odbcinst_ini, text)
        odbc_src = self.common.set_odbc_src(self.primary_root, self.local_path,
                                            self.odbc_lib_path, self.sourcefile)
        self.assertTrue(odbc_src, text)

        text = '-----step6:检查依赖库;expect:成功-----'
        self.log.info(text)
        lib_file = os.path.join(self.odbc_lib_path, 'psqlodbcw.so')
        self.common.check_libfile(self.primary_root, lib_file,
                                  os.path.join(self.local_path, 'lib'))

        text = '-----step7:连接数据库;expect:成功-----'
        self.log.info(text)
        exe_cmd8 = f'source {self.sourcefile};' \
                   f'source {macro.DB_ENV_PATH};' \
                   f'isql -v gaussodbc &'
        self.log.info(exe_cmd8)
        msg8 = self.primary_root.sh(exe_cmd8).result()
        self.log.info(msg8)
        self.assertIn('Connected!', msg8, '执行失败' + text)

        text = '-----step8:编译脚本;expect:成功-----'
        self.log.info(text)
        self.primary_root.scp_put(
            os.path.join(macro.SCRIPTS_PATH, self.compile + '.c'),
            os.path.join(self.local_path, self.compile + '.c'))
        drop_sql = "drop table if exists mediumblob_046;"
        create_sql = "create table mediumblob_046(field_name mediumblob);"
        insert_sql = "insert into t_mediumblob_046 values ('aaa'::blob);"
        select_sql = "select * from t_mediumblob_046;"
        sed_cmd = f"sed -i " \
                  f"'s#drop_sql#{drop_sql}#g' " \
                  f"{os.path.join(self.local_path, self.compile + '.c')};" \
                  f"sed -i 's#create_sql#{create_sql}#g' " \
                  f"{os.path.join(self.local_path, self.compile + '.c')};" \
                  f"sed -i 's#insert_sql#{insert_sql}#g' " \
                  f"{os.path.join(self.local_path, self.compile + '.c')};" \
                  f"sed -i 's#select_sql#{select_sql}#g' " \
                  f"{os.path.join(self.local_path, self.compile + '.c')};"
        self.log.info(sed_cmd)
        res = self.common.get_sh_result(self.primary_root, sed_cmd)
        self.assertEqual('', res, '替换文件内容失败')

        exe_compile = f"source {self.sourcefile}; source {macro.DB_ENV_PATH};" \
                      f"gcc -c " \
                      f"'{os.path.join(self.local_path, self.compile + '.c')}' -o " \
                      f"'{os.path.join(self.local_path, self.compile + '.o')}'; " \
                      f"gcc -o " \
                      f"{os.path.join(self.local_path, self.compile)} " \
                      f"{os.path.join(self.local_path, self.compile + '.o')} " \
                      f"{lib_file};"
        self.log.info(exe_compile)
        msg_compile = self.common.get_sh_result(self.primary_root, exe_compile)
        self.assertEqual('', msg_compile, '编译失败')
        check_file_cmd = f'ls {os.path.join(self.local_path, self.compile)}'
        self.log.info(check_file_cmd)
        check_file_msg = self.common.get_sh_result(self.primary_root,
                                                   check_file_cmd)
        self.assertNotIn('No such file or directory', check_file_msg,
                         '执行失败' + text)

        text = '-----step9:执行脚本;expect:成功-----'
        self.log.info(text)
        exe_cmd9 = f'source {self.sourcefile};source {macro.DB_ENV_PATH};' \
                   f'/./{os.path.join(self.local_path, self.compile)}'
        msg9 = self.primary_root.sh(exe_cmd9).result()
        self.log.info(msg9)
        self.assertEqual(msg9.count('Success!'), 23, '执行失败' + text)

    def tearDown(self):
        text = '-----step10:恢复配置文件中的信息;expect:成功-----'
        self.log.info(text)
        check_cmd = f'mv ' \
                    f'{os.path.join(macro.DB_INSTANCE_PATH, "pg_hba_bak.conf")} ' \
                    f'{os.path.join(macro.DB_INSTANCE_PATH, "pg_hba.conf")};' \
                    f'rm -rf {os.path.join(self.local_path, "odbc.ini")} ' \
                    f'{os.path.join(self.local_path, "odbcinst.ini")} ' \
                    f'{self.sourcefile} ' \
                    f'{os.path.join(self.local_path, self.compile + "*")}'
        self.log.info(check_cmd)
        res = self.common.get_sh_result(self.primary_root, check_cmd)
        sql_cmd = f"drop database if exists {self.db_name};"
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.log.info(sql_res)
        self.log.info('-----重启数据库-----')
        node_num = self.common.get_node_num(self.primary_root)
        self.log.info(node_num)
        if node_num == 1:
            self.sh_primy.execute_gsctl('restart',
                                        self.constant.REBUILD_SUCCESS_MSG)
        else:
            self.sh_primy.execute_gsctl('restart',
                                        self.constant.REBUILD_SUCCESS_MSG,
                                        param="-M primary")
        self.assertEqual('', res, '文件删除失败')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, sql_res,
                      '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} finish-----')
